﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados.Enum;
using System.Data;

/// <summary>
/// Summary description for SectorDAO
/// </summary>
/// 
namespace br.com.SistemaChamados.DAO
{
    public class SectorDAO
    {
        private static string SQL_UPDATE_SECTOR = "UPDATE TBL_SECTOR SET TBL_SECTORDESCRIPTION=@sectordescription, TBL_UNITID=@unitid WHERE TBL_SECTORID=@sectorid";
        private static string SQL_DELETE_SECTOR = " DELETE FROM TBL_SECTOR WHERE TBL_SECTORID = @id";
        private static string SQL_INSERT_SECTOR = "INSERT INTO TBL_SECTOR (TBL_SECTORDESCRIPTION, TBL_UNITID) VALUES (@sectordescription, @unitid)";
        private static string SQL_GET_SECTOR = " SELECT * FROM TBL_SECTOR WHERE TBL_SECTORID = @id";
        private static string SQL_GET_SECTORS = " SELECT * FROM TBL_SECTOR";
        private static string SQL_GET_SECTORS_UNIT = " SELECT * FROM TBL_SECTOR WHERE TBL_UNITID=@unitid";

        public EnumGenericState UpdateSector(Sector sector)
        {
            EnumGenericState resultSector = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_SECTOR, conn);
                command.Parameters.Add(Mapped.Parameter("@sectorid", sector.Id));
                command.Parameters.Add(Mapped.Parameter("@sectordescription", sector.Description));
                command.Parameters.Add(Mapped.Parameter("@unitid", sector.Unit.Id));
                command.ExecuteNonQuery();
                resultSector = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                resultSector = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultSector;
        }

        public EnumGenericState DeleteSector(Sector sector)
        {
            EnumGenericState resultSector = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_SECTOR, conn);
                command.Parameters.Add(Mapped.Parameter("@id", sector.Id));
                command.ExecuteNonQuery();
                resultSector = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                resultSector = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultSector;
        }

        public EnumGenericState InsertSector(Sector sector)
        {
            EnumGenericState resultSector = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_SECTOR, conn);
                command.Parameters.Add(Mapped.Parameter("@sectordescription", sector.Description));
                command.Parameters.Add(Mapped.Parameter("@unitid", sector.Unit.Id));

                command.ExecuteNonQuery();
                resultSector = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                resultSector = EnumGenericState.GENERIC_STATE_FAILED;
                sector = null;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultSector;
        }

        public Sector GetSector(Sector sector)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            Sector resultSector = null;
            Unit unit = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_SECTOR, conn);
                command.Parameters.Add(Mapped.Parameter("@id", sector.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    resultSector = new Sector();
                    unit = new Unit();
                    resultSector.Id = Convert.ToInt32(reader["TBL_SECTORID"]);
                    resultSector.Description = Convert.ToString(reader["TBL_SECTORDESCRIPTION"]);
                    unit.Id = Convert.ToInt32(reader["TBL_UNITID"]);
                    resultSector.Unit = unit;
                }
            }
            catch (Exception ex)
            {
                resultSector = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return resultSector;
        }
        public List<Sector> GetSectors()
        {
            List<Sector> list = new List<Sector>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            Sector sector = null;
            Unit unit = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_SECTORS, conn);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    sector = new Sector();
                    unit = new Unit();
                    sector.Id = Convert.ToInt32(reader["TBL_SECTORID"]);
                    sector.Description = Convert.ToString(reader["TBL_SECTORDESCRIPTION"]);
                    unit.Id = Convert.ToInt32(reader["TBL_UNITID"]);
                    sector.Unit = unit;
                    list.Add(sector);
                }
                return list;
            }
            catch (Exception ex)
            {
                list = null;
                return list;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

        }
        public List<Sector> GetSectorsByUnit(Sector sector)
        {
            List<Sector> list = new List<Sector>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            Sector resultSector = null;
            Unit unit = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_SECTORS_UNIT, conn);
                command.Parameters.Add(Mapped.Parameter("@unitid", sector.Unit.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    resultSector = new Sector();
                    unit = new Unit();
                    resultSector.Id = Convert.ToInt32(reader["TBL_SECTORID"]);
                    resultSector.Description = Convert.ToString(reader["TBL_SECTORDESCRIPTION"]);
                    unit.Id = Convert.ToInt32(reader["TBL_UNITID"]);
                    resultSector.Unit = unit;
                    list.Add(resultSector);
                }
                return list;
            }
            catch (Exception ex)
            {
                list = null;
                return list;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

        }
        public SectorDAO()
        {
            //
            // TODO: Add constructor logic here
            //
        }
    }
}